<?php

class Users
{
	public $pdo;
	
	public function get_completions($user)
	{
		$stmt = $this->pdo->prepare("SELECT COURSES.NAME AS COURSE,
			                          COMPLETIONS.GRADE,
			                          COURSES.CREDITS
			                   FROM COURSES, COMPLETIONS
			                   WHERE COMPLETIONS.STUDENT = ?
			                     AND (COMPLETIONS.GRADE > 0 OR COMPLETIONS.GRADE IS NULL)
			                     AND COURSES.ID = COMPLETIONS.COURSE
			                   ORDER BY COURSES.NAME ASC");
		$stmt->execute(array($user));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}

	public function get_failures($user)
	{
		$stmt = $this->pdo->prepare("SELECT COURSES.NAME AS COURSE, COURSES.CREDITS
			                   FROM COURSES, COMPLETIONS
			                   WHERE COMPLETIONS.STUDENT = ?
			                     AND COMPLETIONS.GRADE = 0
			                     AND COURSES.ID = COMPLETIONS.COURSE
			                   ORDER BY COURSES.NAME ASC");
		$stmt->execute(array($user));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}
	
	public function log_in($username,$password)
	{
		$stmt = $this->pdo->prepare("SELECT ID, PASSHASH FROM USERS WHERE USERNAME = ?");
		$stmt->execute(array($username));
		$result = $stmt->fetchObject();
	
		if($result && strcmp(md5($password),$result->passhash) == 0)
			return $result->id;
		else
			return false;
	}
	
	public function register_into($group,$user)
	{
		$stmt = $this->pdo->prepare("INSERT INTO REGISTRATIONS(STUDENT,GROUPS) VALUES (?,?)");
		return $stmt->execute(array($user,$group));
	}

	public function get_registrations($user)
	{
		$stmt = $this->pdo->prepare("SELECT COURSES.NAME AS COURSE,
			                          INSTANCES.ID AS INSTANCE,
			                          TEACHERS.NAME AS TEACHER,
			                          GROUPS.ID AS GROUP,
			                          SUPERVISERS.NAME AS SUPERVISER
			                   FROM REGISTRATIONS INNER JOIN GROUPS
			                   ON (REGISTRATIONS.STUDENT = ? AND GROUPS.ID = REGISTRATIONS.GROUPS)
			                   INNER JOIN INSTANCES ON (INSTANCES.ID = GROUPS.INSTANCE)
			                   INNER JOIN COURSES ON (COURSES.ID = INSTANCES.COURSE)
			                   LEFT OUTER JOIN USERS AS SUPERVISERS ON (SUPERVISERS.ID = GROUPS.TEACHER)
			                   LEFT OUTER JOIN USERS AS TEACHERS ON (TEACHERS.ID = INSTANCES.TEACHER)
			                   ORDER BY COURSES.NAME, INSTANCES.ID ASC");
		$stmt->execute(array($user));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}

	public function change_group($newgroup,$oldgroup,$user)
	{
		$stmt = $this->pdo->prepare("UPDATE REGISTRATIONS SET GROUPS = ? WHERE GROUPS = ? AND STUDENT = ?");
		return $stmt->execute(array($newgroup,$oldgroup,$user));
	}

	public function cancel_registration($group,$user)
	{
		$stmt = $this->pdo->prepare("DELETE FROM REGISTRATIONS WHERE GROUPS = ? AND STUDENT = ?");
		return $stmt->execute(array($group,$user));
	}

	public function pass($course,$user,$grade,$group)
	{
		if(strlen($grade) == 0)
			$grade = null;
	
		$stmt = $this->pdo->prepare("SELECT 1 FROM COMPLETIONS WHERE STUDENT = ? AND COURSE = ?");
		$stmt->execute(array($user,$course));
		if($result = $stmt->fetchObject())
		{
			$stmt = $this->pdo->prepare("UPDATE COMPLETIONS SET GRADE = ? WHERE STUDENT = ? AND COURSE = ?");
			$stmt->execute(array($grade,$user,$course));
		}
		else
		{
			$stmt = $this->pdo->prepare("INSERT INTO COMPLETIONS (STUDENT,COURSE,ID) VALUES (?,?,?)");
			$stmt->execute(array($user,$course,$grade));
		}
		$stmt = $this->pdo->prepare("DELETE FROM REGISTRATIONS WHERE STUDENT = ? AND GROUPS = ?");
		return $stmt->execute(array($user,$group));
	}
}

?>
